﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IBLL;
using KPIS.GERP.GIMS.IDAL;
using KPIS.GERP.GIMS.DALFactory;
using KPIS.DBM;

namespace KPIS.GERP.GIMS.BLL
{
    public class RequestBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
       
        
        public void Insert(string icm_req_code,string request_name,DateTime wanted_date,DateTime request_date,
            string reason, int from_icm_wh_seq, int icm_res_type_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@icm_req_code", icm_req_code);
                dbManager.AddParameters(1, "@request_name", request_name);
                dbManager.AddParameters(2, "@wanted_date", wanted_date);
                dbManager.AddParameters(3, "@request_date", request_date);
                dbManager.AddParameters(4, "@reason", reason);
                dbManager.AddParameters(5, "@from_icm_wh_seq", from_icm_wh_seq);
                dbManager.AddParameters(6, "@icm_res_type_seq", icm_res_type_seq);
                dbManager.AddParameters(7, "@created_by",1);
                dbManager.AddParameters(8, "@created_when",DateTime.Today  );

                strSQL = "INSERT INTO ICM_Request"
                    + " (icm_req_code, request_name, wanted_date,request_date,reason,"
                    + " from_icm_wh_seq,icm_res_type_seq,created_by,created_when)"
                    + " VALUES(@icm_req_code"
                    + ", @request_name"
                    + ", @wanted_date"
                    + ", @request_date"
                    + ", @reason"
                    + ", @from_icm_wh_seq"
                    + ", @icm_res_type_seq"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        /// <summary>
        /// ขอเลขที่ใบเบิก
        /// </summary>
        /// <returns></returns>
        /// 


        private Boolean checkCurrentMonth(string month_no)
        {
            try
            {
                DataSet ds;
                DataTable dt;
                string sql;
                sql = " select max(icm_req_seq) as seq,mid(icm_req_code,8,2) as m"
+ " from " 
+ " icm_request ";

                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    if (month_no == dt.Rows[0]["m"].ToString())
                    {
                        return true;
                    }
                    else
                    {
                        return false; 
                    }

                }
                else
                {
                    return false;
                }


            }

            catch (Exception ex)
            {

                throw (ex);
             
            }
            finally
            {
                dbManager.Dispose();
            }


        }

        public string getIR_RefNO(string month_no)
        {
            IDBManager dbManager2 = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
 
                dbManager2.Open();

                if (checkCurrentMonth(month_no) == true)
                {

                    DataSet ds;
                    DataTable dt;
                    string sql;
                    sql = " SELECT Max(icm_req_seq) as seq,icm_req_code as code"
    + " FROM"
    + " icm_request";

               
                    ds = dbManager2.ExecuteDataSet(CommandType.Text, sql);
                    dt = ds.Tables[0].Copy();
                    if (dt.Rows.Count > 0)
                    {
                        return dt.Rows[0]["code"].ToString()  ;
                    }
                    else
                    {
                        return "0001";
                    }
                }
                else
                {

                    return "0000";

                }

         

            }

            catch (Exception ex)
            {

                throw (ex);
                return "";
            }
            finally
            {
                dbManager2.Dispose();
            }



        }





        /// <summary>
        /// โหลดประเภทพัสดุ  --- วัสดุ   ครุภัณฑ์ 
        /// </summary>
        /// <returns></returns>
        /// 

        public DataTable SelectInvType()
        {
            try
            {
                DataSet ds;
                DataTable dt;
                string sql;
                sql = " SELECT "
+ " icm_mas_resource_type.icm_res_type_seq as seq,"
+ " icm_mas_resource_type.icm_res_type_name as name"
+ " FROM"
+ " icm_mas_resource_type"
+ " where icm_res_type_seq in(1,2)";
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();

                return dt;

            }
                
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }


        }



        public DataTable SelectWareHouse()
        {
            try
            {
                DataSet ds;
                DataTable dt;
                string sql;
                sql = " SELECT"
+ " wh.icm_wh_seq AS seq,"
+ " wh.icm_wh_name AS name"
+ " FROM"
+ " icm_mas_warehouse AS wh";
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();

                return dt;

            }

            catch (Exception ex)
            {
                throw (ex);
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }


        }


        public void Delete(int seq, int test)
        {


        }









        public DataTable SelectBySeq(int seq)
        {

            try
            {
                DataSet ds = new DataSet();
                DataTable dt;
                string sql;

                sql = "SELECT"
+ " req.icm_req_seq AS seq,"
+ " req.icm_req_code,"
+ " req.job_site_seq,"
+ " req.request_name,"
+ " req.from_icm_wh_seq,"
+ " req.remarks,"
+ " req.request_by,"
+ " req.created_by,"
+ " req.created_when,"
+ " req.updated_by,"
+ " req.updated_when,"
+ " req.request_date,"
+ " req.icm_res_type_seq,"
+ " req.wanted_date,"
+ " if(status='W','รอการยืนยัน','ยืนยันแล้ว') as status,"
+ " req.approved_by,"
+ " req.approved_date,"
+ " type.icm_res_type_name as type_name,"
+ " type.icm_res_type_seq,"
+ " icm_mas_warehouse.icm_wh_name"
+ " FROM"
+ " icm_request AS req"
+ " Inner Join icm_mas_resource_type AS type ON req.icm_res_type_seq = type.icm_res_type_seq"
+ " Inner Join icm_mas_warehouse ON req.from_icm_wh_seq = icm_mas_warehouse.icm_wh_seq"
+ " where req.icm_req_seq=" + seq ;

                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();

                return dt;
            }

            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }


        }

        public DataTable   Select()
        {

            try
            {
                DataSet ds = new DataSet(); 
                DataTable dt;
                string sql;

                sql = "SELECT"
+ " req.icm_req_seq AS seq,"
+ " req.icm_req_code,"
+ " req.job_site_seq,"
+ " req.request_name,"
+ " req.from_icm_wh_seq,"
+ " req.remarks,"
+ " req.request_by,"
+ " req.created_by,"
+ " req.created_when,"
+ " req.updated_by,"
+ " req.updated_when,"
+ " req.request_date,"
+ " req.icm_res_type_seq,"
+ " req.wanted_date,"
+ " if(status='W','รอการยืนยัน','ยืนยันแล้ว') as status,"
+ " req.approved_by,"
+ " req.approved_date,"
+ " type.icm_res_type_name as type_name,"
+ " type.icm_res_type_seq"
+ " FROM"
+ " icm_request AS req"
+ " Inner Join icm_mas_resource_type AS type ON req.icm_res_type_seq = type.icm_res_type_seq";

                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();  

                return dt;
            }

            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }


                    }





        /// <summary>
        /// โหลดประเภทการเคลื่อนไหว รับโอน  บริจาค  ยอดยกมา....... 
        /// </summary>
        public DataTable SelectMMTypeInv()
        {

            try
            {
                DataSet ds = new DataSet();
                DataTable dt;
                string sql;

                sql = "select icm_mm_type_seq as seq,icm_mm_type_name as name from icm_movement_type";
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();

                return dt;
            }

            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }



        }     


    }
}
